package com.zhangtai.modules.dao;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhangtai.modules.dto.EmployeeSecurityDto;
import com.zhangtai.modules.dto.SecurityDto;
import com.zhangtai.modules.entity.security.SecurityEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2020-08-17 17:30:20
 */
@Mapper
public interface SecurityDao extends BaseMapper<SecurityEntity> {


    @Select("<script>SELECT " +
            "t1.security_id, t1.security_name, t1.city_name, t1.unit_name,t1.status, t1.type, t1.unit_id,t1.contract_main_id," +
            "IF(t2.id is not null,'已汇缴','未汇缴')  confirmStatus, " +
            "<if test='type==0'>(SELECT count(1) FROM tb_employee_security WHERE security_id = t1.security_id AND is_delete = 0) AS number </if>" +
            "<if test='type==1'>(SELECT count(1) FROM tb_employee_security WHERE funds_id = t1.security_id AND is_delete = 0) AS number </if>" +
            "FROM " +
            " tb_security t1 " +
            "LEFT JOIN tb_security_batch t2 ON t1.security_id = t2.security_id " +
            "and t2.confirm_time &lt;= #{endTime} " +
            "AND t2.confirm_time &gt;= #{startTime} " +
            "<where> t1.is_delete = 0   " +
            "<if test='securityName!=null and securityName!=&apos;&apos;'>and t1.security_name = #{securityName} </if>" +
            "<if test='unitName!=null and unitName!=&apos;&apos;'>and t1.unit_name  like '%${unitName}%' </if>" +
            "<if test='cityName!=null and cityName!=&apos;&apos;'>and t1.city_name like '%${cityName}%' </if>" +
            "<if test='status!=null'>and t1.status = #{status} </if>" +
            "<if test='number!=null'>and t1.number &gt;= #{number} </if>" +
            "<if test='type!=null'>and t1.type = #{type} </if>" +
            "AND t1.contract_main_id  IN" +
            "<foreach collection=\"contractIds\" item=\"item\" index=\"index\" " +
            "open=\"(\" separator=\",\" close=\")\">" +
            "#{item}" +
            "</foreach>" +
            "</where>" +
            "</script>")
    List<SecurityDto> getSecurityList(@Param(value = "contractIds") List<Long> contractIds,
                                      @Param("startTime") String startTime,
                                      @Param("endTime") String endTime,
                                      @Param(value = "securityName") String securityName,
                                      @Param(value = "unitName") String unitName,
                                      @Param(value = "cityName") String cityName,
                                      @Param(value = "status") Integer status,
                                      @Param(value = "number") Long number,
                                      @Param(value = "type") Integer type,
                                      Page page);

    @Select("<script>SELECT " +
            "t1.security_id, t1.security_name, t1.city_name, t1.unit_name,t1.status, t1.type, t1.unit_id,t1.contract_main_id," +
            "IF(t2.id is not null,'已汇缴','未汇缴')  confirmStatus, " +
            "(SELECT count(1) FROM tb_employee_security WHERE security_id = t1.security_id AND is_delete = 0) AS number " +
            "FROM " +
            " tb_security t1 " +
            "LEFT JOIN tb_security_batch t2 ON t1.security_id = t2.security_id " +
            "and t2.confirm_time &lt;= #{endTime} " +
            "AND t2.confirm_time &gt;= #{startTime} " +
            "<where> t1.is_delete = 0  and t1.type=0 " +
            "<if test='unitName!=null and unitName!=&apos;&apos;'>and t1.unit_name  like '%${unitName}%' </if>" +
            "<if test='cityName!=null and cityName!=&apos;&apos;'>and t1.city_name  like '%${cityName}%' </if>" +
            " AND t1.contract_main_id  IN " +
            "<foreach collection=\"contractIds\" item=\"item\" index=\"index\" " +
            "open=\"(\" separator=\",\" close=\")\">" +
            "#{item}" +
            "</foreach>" +
            "</where>" +
            "</script>")
    List<SecurityDto> getSecurityListExcel(@Param(value = "contractIds") List<Long> contractIds
            , @Param(value = "cityName") String cityName, @Param(value = "unitName") String unitName, @Param("startTime") String startTime, @Param("endTime") String endTime);


    @Select("<script>SELECT " +
            "t1.security_id, t1.security_name, t1.city_name, t1.unit_name,t1.status, t1.type, t1.unit_id,t1.contract_main_id," +
            "IF(t2.id is not null,'已汇缴','未汇缴')  confirmStatus, " +
            "(SELECT count(1) FROM tb_employee_security WHERE funds_id = t1.security_id AND is_delete = 0) AS number " +
            " FROM " +
            " tb_security t1 " +
            "LEFT JOIN tb_security_batch t2 ON t1.security_id = t2.security_id " +
            " and t2.confirm_time &lt;= #{endTime} " +
            " AND t2.confirm_time &gt;= #{startTime} " +
            "<where> t1.is_delete = 0  and t1.type=1  " +
            "<if test='unitName!=null and unitName!=&apos;&apos;'>and t1.unit_name  like '%${unitName}%' </if>" +
            "<if test='cityName!=null and cityName!=&apos;&apos;'>and t1.city_name  like '%${cityName}%' </if>" +
            " AND t1.contract_main_id  IN " +
            "<foreach collection=\"contractIds\" item=\"item\" index=\"index\" " +
            "open=\"(\" separator=\",\" close=\")\">" +
            "#{item}" +
            "</foreach>" +
            "</where>" +
            "</script>")
    List<SecurityDto> getFundListExcel(@Param(value = "contractIds") List<Long> contractIds
            , @Param(value = "cityName") String cityName, @Param(value = "unitName") String unitName, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("<script>SELECT " +
            "t1.*," +
            "IF(t2.id is not null,'已汇缴','未汇缴')  confirmStatus " +
            "FROM " +
            " tb_security t1 " +
            "LEFT JOIN tb_security_batch t2 ON t1.security_id = t2.security_id " +
            "<where> t1.is_delete = 0   " +
            "<if test='contractIds!=null'>AND t1.contract_main_id IN " +
            "<foreach collection=\"contractIds\" item=\"item\" index=\"index\" " +
            "open=\"(\" separator=\",\" close=\")\">" +
            "#{item}" +
            "</foreach></if>" +
            "<if test='contractIds==null'>AND t1.contract_main_id ='999999' </if> " +

            "</where>" +
            "</script>")
    List<SecurityDto> getSecurityListUser(@Param(value = "contractIds") List<Long> contractIds);

    @Select("<script>" +
            " select record_id from tb_security_record where security_id = #{securityId} and is_check = 1 and is_confirm = 0 and is_delete = 0" +
            "</script>")
    List<String> getAllSecurity(@Param(value = "securityId") Long id);

    @Select("<script>Select name,code,endowment_insurance,unemployment_insurance,medical_insurance,injury_insurance,larger_medical,maternity_insurance,disabled_employment from tb_employee_security t1 " +
            "<where> t1.is_delete = 0 " +
            "<if test='security!=null'> and security_id =#{security} </if>" +
            "</where>" +
            "</script>")
    List<EmployeeSecurityDto> getUserSecurityList(@Param(value = "security") Long id);
}
